#include "datamodel.h"
#include <QDate>

dataModel::dataModel()
{
    if(QSqlDatabase::contains("CT11"))
        mainDB = QSqlDatabase::database("CT11");
    else
        mainDB = QSqlDatabase::addDatabase("QSQLITE", "CT11");

    mainDB.setDatabaseName("database.db");

    if(!mainDB.isOpen())
    {
        if(!mainDB.open())
        {
            qDebug()<<"Failed to connect:"<<mainDB.lastError();
        }
    }

    QSqlQuery query(mainDB);

    if(!query.exec("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='dutInfo'"))
    {
        qDebug()<<"check dutInfo:"<<query.lastError();
    }
    else
    {
        query.next();

        if (0 == query.value(0).toInt())
        {
            createDefaultTable();
        }
    }

    dateIsUpdated = true;

    recordListIndex = 0;

    beginDate = "1970-01-01";

    endDate = QDate::currentDate().toString("yyyy-MM-dd");
}
dataModel::~dataModel(){
    mainDB.close();
}

void dataModel::createDefaultTable()
{
    QSqlQuery sql_query(mainDB);

    // recordID eq to rowID of SN in table sn
    QString create_sql1 = "create table dutInfo ("
            "SN varchar(20),"
            "PN varchar(20),"
            "Date varchar(20),"
            "Ratio varchar(20),"
            "Precision varchar(10),"
            "PowerFactor varchar(10)"
            ")";

    QString create_sql2 = "create table errData ("
            "testID int,"
            "NLoad varchar(10),"
            "errN1Ratio varchar(10),"
            "errN5Ratio varchar(10),"
            "errN20Ratio varchar(10),"
            "errN100Ratio varchar(10),"
            "errN120Ratio varchar(10),"
            "errN1Phase varchar(10),"
            "errN5Phase varchar(10),"
            "errN20Phase varchar(10),"
            "errN100Phase varchar(10),"
            "errN120Phase varchar(10),"
            "MLoad varchar(10),"
            "errM1Ratio varchar(10),"
            "errM5Ratio varchar(10),"
            "errM20Ratio varchar(10),"
            "errM100Ratio varchar(10),"
            "errM1Phase varchar(10),"
            "errM5Phase varchar(10),"
            "errM20Phase varchar(10),"
            "errM100Phase varchar(10)"
            ")";

    if(!sql_query.exec(create_sql1))
    {
        qDebug()<<"Create table dutInfo:"<<sql_query.lastError();
    }

    if(!sql_query.exec(create_sql2))
    {
        qDebug()<<"Create table errData:"<<sql_query.lastError();
    }
}

// return the rowid of new records
int dataModel::newRecord(QString SN, QString PN, QString Date,
                              QString Ratio, QString Precision,QString PowerFactor)
{
    QSqlQuery sql_query(mainDB);

    QString find_sql = "select * from dutInfo where SN='"+ SN + "'";

    qDebug()<<find_sql;

    if(sql_query.exec(find_sql)) // read and write db error
    {
        if(sql_query.next()) // there is already a record for SN
        {
            qDebug()<<"result already exists! SN="<<SN;
            return -1;
        }
    }

    sql_query.finish();

    QSqlQuery query_insert(mainDB);

    QString insert_sql = "insert into dutInfo values('";
    insert_sql += SN;
    insert_sql += "','";
    insert_sql += PN;
    insert_sql += "','";
    insert_sql += Date;
    insert_sql += "','";
    insert_sql += Ratio;
    insert_sql += "','";
    insert_sql += Precision;
    insert_sql += "','";
    insert_sql += PowerFactor;
    insert_sql += "');";

    qDebug()<<insert_sql;

    if(!query_insert.exec(insert_sql))
    {
        qDebug()<<"add new record:"<<query_insert.lastError();

        return -2;
    }
    else
    {
        query_insert.exec("SELECT last_insert_rowid();");

        query_insert.next();

        qDebug()<<query_insert.value(0).toInt();

        return query_insert.value(0).toInt();
    }
}

bool dataModel::saveErrorData(QStringList strList)
{
    QString reqStr = "insert into errData values('";

     QStringListIterator listInterator(strList);

    while(listInterator.hasNext())
    {
        reqStr += listInterator.next();
        reqStr += "','";
    }

    reqStr.resize(reqStr.length()-2);

    reqStr += ")";

    qDebug()<<reqStr;

    QSqlQuery sql_query(mainDB);

    if(!sql_query.exec(reqStr)) // read and write db error
    {
        qDebug()<<sql_query.lastError();

        return false;
    }
    else
    {
        return true;
    }
}

int dataModel::readAllData()
{
    QSqlQuery queryCount(mainDB);

    static int lastRowID = -1;

    if( ! dateIsUpdated)
    {
        queryCount.exec("SELECT last_insert_rowid();");

        queryCount.next();

        // here reading share a sql connection with database writing
        // so, check the last_insert_rowid() to find if there is a new record
        if (lastRowID == queryCount.value(0).toInt())
        {
            return recordList.length();
        }
        else
        {
            lastRowID = queryCount.value(0).toInt();
        }
    }
    else
    {
        recordListIndex = -1;

        dateIsUpdated = false;
    }

    qDebug()<<"RecordList is updated!";

    QString reqStr = "select SN from dutInfo where date between '";
    reqStr += beginDate;
    reqStr += "'and'";
    reqStr += endDate + "'";

    qDebug()<<reqStr;

    if(!queryCount.exec(reqStr))
    {
        qDebug()<<queryCount.lastError();

        recordList.clear();

        queryCount.finish();

        return -2;
    }
    else
    {
        refreshed = true;

        recordList.clear();

        while(queryCount.next())
        {
            recordList<<queryCount.value(0).toString();
        }

        if(recordListIndex == -1
                || recordListIndex > recordList.length() - 1) //give a default value when start
        {
            recordListIndex = recordList.length() -1;
        }
    }

    return recordList.length();
}

int dataModel::next()
{
    if ( recordList.isEmpty() ) return 0;

    if(recordListIndex != recordList.length() - 1)
    {
        recordListIndex++;
    }
    else
    {
        recordListIndex = 0;
    }

    qDebug()<<recordListIndex;

    refreshed = true;

    return recordListIndex;
}

int dataModel::pre()
{
    if(recordList.isEmpty()) return 0;

    if(recordListIndex != 0)
    {
        recordListIndex --;
    }
    else
    {
        recordListIndex = recordList.length() - 1;
    }

    qDebug()<<recordListIndex;

    refreshed = true;

    return recordListIndex;
}

int dataModel::findBySN(QString SN)
{
    QSqlQuery sql_query(mainDB);

    QString find_sql = "select * from dutInfo where SN='"+ SN + "'";

    qDebug()<<find_sql;

    if(sql_query.exec(find_sql)) // read and write db error
    {
        if(sql_query.next()) // there is already a record for SN
        {
            qDebug()<<"Find the record:"<<sql_query.value(0).toString();

            recordList.clear();

            recordList<<sql_query.value(0).toString();

            recordListIndex = 0;

            refreshed = true;

            return 1;
        }
        else
        {
            recordList.clear();

            recordListIndex = -1;

            return -1;
        }
    }
    else
    {
        return -2;
    }
}

void dataModel::getCurrentRecordInfo(QStringList &result)
{
    refreshed = false;

    if (recordList.isEmpty()) return;

    QSqlQuery queryCurrentRecordInfo(mainDB);

    qDebug()<<recordListIndex;

    QString SN = recordList.at(recordListIndex);

    qDebug()<<SN;

    if(!queryCurrentRecordInfo.exec("select * from dutInfo where SN='"+SN+"'")) // read and write db error
    {
        qDebug()<<queryCurrentRecordInfo.lastError();

        return;
    }
    else
    {
        if( queryCurrentRecordInfo.next())
        {
            result<< queryCurrentRecordInfo.value(0).toString();
            result<< queryCurrentRecordInfo.value(1).toString();
            result<< queryCurrentRecordInfo.value(2).toString();
            result<< queryCurrentRecordInfo.value(3).toString();
            result<< queryCurrentRecordInfo.value(4).toString();
            result<< queryCurrentRecordInfo.value(5).toString();
        }
    }
}

// rowID has been aborted
void dataModel::getCurrentRecordData(int aborted, QStringList &result)
{
    if ( recordList.isEmpty() ) return;

    QSqlQuery queryCurrentRecordError(mainDB);

    int rowID = 0;

    QString SN = recordList.at(recordListIndex);

    // get rowID from dutInfo
    if(!queryCurrentRecordError.exec("select rowID from dutInfo where SN='"+SN+"'"))
    {
        qDebug()<<queryCurrentRecordError.lastError();

        queryCurrentRecordError.finish();

        return;
    }
    else
    {
        queryCurrentRecordError.next();

        rowID = queryCurrentRecordError.value(0).toInt();
    }

    queryCurrentRecordError.finish();

    QString str="select * from errData where testID='";
    str += QString::number(rowID);
    str += "'";

    qDebug()<<str<<aborted;

    if(!queryCurrentRecordError.exec(str)) // read and write db error
    {
        qDebug()<<queryCurrentRecordError.lastError();

        return;
    }
    else
    {
        if(queryCurrentRecordError.next())
        {
            for(int i = 1; i < 21; i++)
            {
                result<<queryCurrentRecordError.value(i).toString();
            }
        }
    }
}

void dataModel::deleteBySN(QString SN)
{
    if (recordList.isEmpty()) return;

    int rowID = 0;

    QSqlQuery query(mainDB);

    QString str="select rowID from dutInfo where SN='";
    str += SN;
    str += "'";

    if(!query.exec(str)) // read and write db error
    {
        qDebug()<<query.lastError();

        return;
    }
    else
    {
        query.next();

        rowID = query.value(0).toInt();

        str="delete from dutInfo where SN='";
        str += SN;
        str += "'";

        query.exec(str);

        str="delete from errData where testID='";
        str += QString::number(rowID);
        str += "'";

        query.exec(str);

        int index = recordList.indexOf(SN);

        recordList.removeAt(index);

        if(recordListIndex == recordList.length())
        {
            recordListIndex--;
        }

        refreshed = true;
    }
}

void dataModel::deleteAll()
{
    if (recordList.length() == 0) return;

    QSqlQuery query(mainDB);

    QString str="delete from dutInfo";

    if(!query.exec(str)) // read and write db error
    {
        qDebug()<<query.lastError();

        return;
    }
    else
    {
        str="delete from errData";

        query.exec(str);
    }

    recordList.clear();

    recordListIndex = 0;

    readAllData();
}

void dataModel::readRecord(int index, QStringList &record)
{
    QSqlQuery queryCurrentRecordInfo(mainDB);

    qDebug()<<"read Record:"<<index;

    QString SN = recordList.at(index);

    qDebug()<<SN;

    if(!queryCurrentRecordInfo.exec("select * from dutInfo where SN='"+SN+"'")) // read and write db error
    {
        qDebug()<<queryCurrentRecordInfo.lastError();

        return;
    }
    else
    {
        if( queryCurrentRecordInfo.next())
        {
            record<< queryCurrentRecordInfo.value(0).toString();
            record<< queryCurrentRecordInfo.value(1).toString();
            record<< queryCurrentRecordInfo.value(2).toString();
            record<< queryCurrentRecordInfo.value(3).toString();
            record<< queryCurrentRecordInfo.value(4).toString();
            record<< queryCurrentRecordInfo.value(5).toString();
        }
    }

    QSqlQuery queryCurrentRecordError(mainDB);

    int rowID = 0;

    // get rowID from dutInfo
    if(!queryCurrentRecordError.exec("select rowID from dutInfo where SN='"+SN+"'"))
    {
        qDebug()<<queryCurrentRecordError.lastError();

        queryCurrentRecordError.finish();

        return;
    }
    else
    {
        queryCurrentRecordError.next();

        rowID = queryCurrentRecordError.value(0).toInt();
    }

    queryCurrentRecordError.finish();

    QString str="select * from errData where testID='";
    str += QString::number(rowID);
    str += "'";

    qDebug()<<str;

    if(!queryCurrentRecordError.exec(str)) // read and write db error
    {
        qDebug()<<queryCurrentRecordError.lastError();

        return;
    }
    else
    {
        if(queryCurrentRecordError.next())
        {
            for(int i = 1; i < 21; i++)
            {
                record<<queryCurrentRecordError.value(i).toString();
            }
        }
    }
}
